package com.hnust.dao;

import com.hnust.domain.Class;
import com.hnust.domain.record.SearchStudentInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import com.hnust.domain.record.ClassRecord;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 *创建人：张汝往
 *创建时间：2020/10/6
 *描述：
 */
@Mapper
@Repository
public interface ClassDao {

    //根据学院ID获取班级总数
    @Select("select count(*) from class where col_id=#{collegeId}")
    @ResultType(Integer.class)
    public Integer findClassCountByCollegeId(String collegeId);

    //根据学院ID获取所有班级
    @Select("select * from class where col_id=#{collegeId}")
    @ResultType(Class.class)
    public List<Class> findClassByCollegeId(String collegeId);

    //根据学院ID获取所有年级
    @Select("select grade from class where col_id=#{collegeId} group by grade ")
    @ResultType(String.class)
    public List<String> findAllGrade(String collegeId);


    //根据年级(可能为空)和学院ID获取所有专业ID
    @SelectProvider(type =findAllMajorIdByGradeProvider.class ,method="findAllMajorIdByGradeSql")
    @ResultType(String.class)
    public List<String> findAllMajorIdByGrade(String grade,String collegeId);
    class findAllMajorIdByGradeProvider{
        public String findAllMajorIdByGradeSql(String grade,String collegeId){
            String sql="select maj_id from class where ";
            if(grade!=null&&!grade.equals("")){
                sql=sql+"grade=#{grade} and ";
            }
            sql=sql+"col_id=#{collegeId} group by maj_id";
            return sql;
        }
    }

    //根据年级（可能为空），专业（可能为空）获取所有班级的班号
    @SelectProvider(type = findAllClaNumberByMajorIdProvider.class,method = "findAllClaNumberByMajorIdSql")
    @ResultType(String.class)
    public List<String> findAllClaNumberByMajorId(String majorId,String grade,String collegeId);
    class findAllClaNumberByMajorIdProvider{
        public String findAllClaNumberByMajorIdSql(String majorId,String grade,String collegeId){
            String sql="select cla_number from class where 1=1 ";
            if(majorId!=null){
                sql=sql+" and maj_id=#{majorId}";
            }
            if(grade!=null&&!grade.equals("")){
                sql=sql+" and grade=#{grade}";
            }
            sql=sql+"and col_id=#{collegeId} group by cla_number";
            return sql;
        }
    }

    //根据年级（可能为空），专业（可能为空）获取所有班级的班号
    @SelectProvider(type = findAllClaNumberByMajorIdProviderNew.class,method = "findAllClaNumberByMajorIdSql")
    @ResultType(Map.class)
    public List<Map<String,String>> findAllClaNumberByMajorIdNew(String majorId,String grade,String collegeId);
    class findAllClaNumberByMajorIdProviderNew{
        public String findAllClaNumberByMajorIdSql(String majorId,String grade,String collegeId){
            String sql="select cla_id,cla_number from class where 1=1 ";
            if(majorId!=null){
                sql=sql+" and maj_id=#{majorId}";
            }
            if(grade!=null&&!grade.equals("")){
                sql=sql+" and grade=#{grade}";
            }
            sql=sql+"and col_id=#{collegeId} group by cla_number";
            return sql;
        }
    }

    //根据班级ID获取所有班级
    @Select("select * from class where cla_id=#{claId}")
    @ResultType(Class.class)
    public Class getClassById(String claId);
    public List<String> findAllClaNumberByMajorId(String majorId);

    //获取学校所有年级
    @Select("select grade from class group by grade ")
    @ResultType(String.class)
    public List<String> getGrades();

    //根据学院Id和年纪，获取所有专业
    @Select("select major.name from class,major where class.grade=#{grade} and major.col_id=class.col_id and class.col_id=#{collegeId} and class.maj_id=major.maj_id group by major.name")
    @ResultType(String.class)
    public List<String> getMajors(@Param("collegeId") String collegeId, @Param("grade") String grade);

    //根据学院Id和年纪，获取所有专业
    @Select("select major.name from major where col_id=#{collegeId}")
    @ResultType(String.class)
    public List<String> getMajorNames(@Param("collegeId") String collegeId);

    //根据年纪和专业，获取所有班级号
    @Select("select class.cla_number from class,major where class.grade=#{grade} and major.name=#{major} and major.maj_id=class.maj_id")
    @ResultType(String.class)
    public List<String> getNumbers(@Param("grade")String grade,@Param("major") String major);

    //获取学院所有的班级信息
    @Select("select class.cla_id as classId,class.grade as grade,class.cla_number as number,major.name as major from class,major where class.maj_id=major.maj_id and class.col_id=#{collegeId} limit #{currentPage},#{rows}")
    @ResultType(ClassRecord.class)
    public List<ClassRecord> getClasses(@Param("collegeId")String collegeId, @Param("currentPage") Integer currentPage,@Param("rows") Integer rows);

    //获取学院所有班级数量
    @Select("select count(*) from class where col_id=#{collegeId}")
    @ResultType(Integer.class)
    public Integer getClassesNum(@Param("collegeId")String collegeId);

    //查询指定班级
//    @Select("select class.cla_id as classId,class.grade as grade,class.cla_number as number,major.name as major from class,major where " +
//            "class.maj_id=major.maj_id and class.col_id=#{collegeId} and class.grade=#{classRecord.grade} and major.name=#{classRecord.major} and class.cla_number=#{classRecord.number}" +
//            "limit #{currentPage},#{rows}")
    @SelectProvider(type = ClassDao.queryClassesClass.class,method = "queryClassesSql")
    @ResultType(ClassRecord.class)
    public List<ClassRecord> queryClasses(@Param("collegeId") String collegeId,@Param("classRecord") ClassRecord classRecord,@Param("currentPage") Integer currentPage,@Param("rows") Integer rows);
    class queryClassesClass{
        public String queryClassesSql(@Param("collegeId") String collegeId,@Param("classRecord") ClassRecord classRecord,@Param("currentPage") Integer currentPage,@Param("rows") Integer rows){
            String sql="select class.cla_id as classId,class.grade as grade,class.cla_number as number,major.name as major from class,major where " +
                    "class.maj_id=major.maj_id ";
            if(classRecord.getGrade().length() != 0) {
                sql = sql + " and class.grade=" + "'" + classRecord.getGrade() + "'";
            }
            if(classRecord.getMajor().length() != 0){
                sql = sql + " and major.name=" + "'" +classRecord.getMajor() + "'";
            }
            if(classRecord.getNumber().length() != 0){
                sql = sql + " and class.cla_number=" + "'" + classRecord.getNumber() + "'";
            }
            sql = sql + " limit " + currentPage + ", " + rows;
//            System.out.println(sql);
            return sql;
        }
    }

    //返回查询到的班级数量
    @Select("select count(*) from class,major where class.maj_id=major.maj_id and class.col_id=#{collegeId} and class.grade=#{classRecord.grade} and major.name=#{classRecord.major} and class.cla_number=#{classRecord.number}")
    @ResultType(Integer.class)
    public Integer queryClassesNum(@Param("collegeId") String collegeId,@Param("classRecord") ClassRecord classRecord);

    //添加班级
    @Insert("insert into class(cla_id,grade,cla_number,maj_id,col_id) values(#{cla_id},#{grade},#{cla_number},#{maj_id},#{col_id})")
    public Boolean addClasses(Map map);

    //删除班级
    @Delete("delete from class where grade=#{grade} and maj_id=#{majId}")
    public Boolean deleteClasses(@Param("grade") String grade,@Param("majId") String majId);

    @Select("select grade from class where cla_id=#{id}")
    @ResultType(String.class)
    public String getGradeById(String id);

    @Select("select cla_number from class where cla_id=#{id}")
    @ResultType(String.class)
    public String getClaNumber(String id);

    @Select("select major.name from major,class where class.maj_id=major.maj_id and class.cla_id=#{id}")
    @ResultType(String.class)
    public String getMajorNameById(String id);

    /*
     * @Author Tonited
     * @Description //TODO 根据专业id和班号获取班级id
     * @Date 9:59 2020/10/30
     * @Param [majorId, num]
     * @return java.lang.String
     **/
    @Select({"select cla_id from class where maj_id=#{majorId} and cla_number=#{num} and grade=#{grade}"})
    @ResultType(String.class)
    String getClaIdByMajorIdAndNum(String majorId, String num, String grade);

    //获取年纪、专业、班级名称
    @Select("SELECT c.grade grade,c.maj_id maj_id,m.name major,count(*) number from class c INNER JOIN major m ON c.maj_id=m.maj_id WHERE c.col_id=#{collegeId} group by c.grade,c.maj_id ORDER BY c.grade,m.name LIMIT #{page},#{pageNum}")
    @ResultType(Map.class)
    List<Map> getAllClass(String collegeId,int page,int pageNum);

    //获取年纪、专业、班级名称的数量
    @Select("SELECT count(*) from (SELECT c.cla_id,c.maj_id,m.name ,count(*) from class c INNER JOIN major m ON c.maj_id=m.maj_id WHERE c.col_id=1 group by c.grade,c.maj_id ORDER BY c.grade,m.name) m")
    @ResultType(Integer.class)
    Integer getAllClassNum(String collegeId);

    //根据专业，年纪获取班级
    @Select("SELECT cla_number from class WHERE maj_id=#{maj_id} and grade=#{grade} ORDER BY cla_number")
    @ResultType(String.class)
    List<String> getClassNumber(String maj_id,String grade);
}
